package br.unicentro.lynx.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.unicentro.lynx.model.factory.ConnectionFactory;
import br.unicentro.lynx.model.pojo.Book;

public class BookDao {
	private String sql;
	private Connection connection;
	private PreparedStatement statement;
	private ResultSet result;

	public void insert(Book book) {
		open();
		this.sql = "INSERT INTO Books (isbn_book, title_book, edition_book, copyright_book, volume_book, id_area_book, id_publisher_book, id_collection_book) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setString(1, book.getIsbnBook());
			this.statement.setString(2, book.getTitleBook());
			this.statement.setInt(3, book.getEditionBook());
			this.statement.setInt(4, book.getCopyrightBook());
			this.statement.setInt(5, book.getVolumeBook());
			this.statement.setInt(6, book.getIdAreaBook());
			this.statement.setInt(7, book.getIdPublisherBook());
			this.statement.setInt(8, book.getIdCollectionBook());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public void update(Book book) {
		open();
		this.sql = "UPDATE Books SET isbn_book = ?, title_book = ?, edition_book = ?, copyright_book = ?, volume_book = ?, id_area_book = ?, id_publisher_book = ?, id_collection_book = ?  WHERE id_author = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setString(1, book.getIsbnBook());
			this.statement.setString(2, book.getTitleBook());
			this.statement.setInt(3, book.getEditionBook());
			this.statement.setInt(4, book.getCopyrightBook());
			this.statement.setInt(5, book.getVolumeBook());
			this.statement.setInt(6, book.getIdAreaBook());
			this.statement.setInt(7, book.getIdPublisherBook());
			this.statement.setInt(8, book.getIdCollectionBook());
			this.statement.setInt(9, book.getIdBook());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public void delete(Book book) {
		open();
		this.sql = "DELETE FROM Books WHERE id_book = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setInt(1, book.getIdBook());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public Book search(Integer id_book) {
		open();
		this.sql = "SELECT * FROM Books WHERE id_book = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setInt(1, id_book);
			this.result = this.statement.executeQuery();
			if (this.result.next()) {
				return new Book(result.getInt("id_book"),
						result.getString("isbn_book"),
						result.getString("title_book"),
						result.getInt("edition_book"),
						result.getInt("copyrigt_book"),
						result.getInt("volume_book"),
						result.getInt("id_area_book"),
						result.getInt("id_publisher_boo"),
						result.getInt("id_collection_book"));
			} else {
				return null;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public ArrayList<Book> list() {
		open();
		this.sql = "SELECT * FROM Books";
		ArrayList<Book> books = new ArrayList<Book>();
		try {
			this.statement = connection.prepareStatement(sql);
			this.result = this.statement.executeQuery();
			while (this.result.next()) {
				books.add(new Book(result.getInt("id_book"), result
						.getString("isbn_book"),
						result.getString("title_book"), result
								.getInt("edition_book"), result
								.getInt("copyrigt_book"), result
								.getInt("volume_book"), result
								.getInt("id_area_book"), result
								.getInt("id_publisher_boo"), result
								.getInt("id_collection_book")));
			}
			return books;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				this.result.close();
				close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	private void open() {
		this.connection = ConnectionFactory.getConnection(
				ConnectionFactory.URL, ConnectionFactory.USER,
				ConnectionFactory.PASSWORD);
	}

	private void close() {
		try {
			this.statement.close();
			this.connection.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}
